/*
	EXEC SP_DETAILED_TRANSACTION_REPORT_BY_PRODUCT 0,'11/01/2009', '11/18/2009', 'Die Bank', '0', 0, 1
	EXEC SP_DETAILED_TRANSACTION_REPORT_BY_PRODUCT 0,'10/04/2010', '10/31/2010', 'FG', '0', 0, 1
*/

ALTER PROC SP_DETAILED_TRANSACTION_REPORT_BY_PRODUCT  @BASE_PRODUCT_ID INT, @FROM_DATE DATETIME, @TO_DATE DATETIME, @PROCESS_TYPE VARCHAR(50), @VENDOR_NAME VARCHAR(50), @PRODUCT_ID INT, @BY_LOT INT
AS
BEGIN
	
	CREATE TABLE #RESULTS_TABLE ( 
 				PART_NUMBER VARCHAR(50),PROCESSSTEP_ID INT, LOT_NUMBER VARCHAR(50),
 				BEGINING_QTY INT, 
				IN_QTY INT,
				OUT_QTY INT,
				IN_FROM_FG INT, --TR
				IN_FROM_TR INT, --FG
				IN_FROM_RMA INT,
				IN_From_Other INT,
				Transfer_To_Bulk INT,
				SHIPMENTS_TO_CUSTOMER INT, --FG
				SCRAP_QTY INT, 
				OUT_TO_OTHER INT,
				ENDING_QTY INT,
				DB_PART_NUMBER VARCHAR(50) )--ASSEMBLY 
	
	DECLARE @WIP_RESULT_STRING VARCHAR(1200)
	DECLARE @TRANSIT_RESULT_STRING VARCHAR(1200)
	DECLARE @OUT_QTY_STRING VARCHAR(1200)
	DECLARE @OUT_TO_OTHER_QTY_STRING VARCHAR(1200)
	DECLARE @TRANSFER_TO_BULK_STRING VARCHAR(1200)
	DECLARE @IN_QTY_STRING VARCHAR(1200)
	DECLARE @IN_FROM_OTHER_QTY_STRING VARCHAR(1200)

	DECLARE @CURSOR_STRING NVARCHAR(1000)
	
	CREATE TABLE #BEGINING_WIP_RESULTS ( LOT_NUMBER VARCHAR(50), QTY_IN INT, PART_NUMBER VARCHAR(50) )
	CREATE TABLE #ENDING_WIP_RESULTS ( LOT_NUMBER VARCHAR(50), QTY_IN INT, PART_NUMBER VARCHAR(50) )
	
	DECLARE @BEGINIG_AS_OF_DATE DATETIME
	DECLARE @ENDING_AS_OF_DATE DATETIME
	DECLARE @TEMP_VARCHAR VARCHAR(50)
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	DECLARE @TEMP_CURSOR CURSOR
	DECLARE @TEMP_PROCESSSTEP_ID VARCHAR(60)
	DECLARE @OTHER_PROCESSSTEP_ID INT
	SET @BEGINIG_AS_OF_DATE = DATEADD( ss, -1, @FROM_DATE )
	SET @ENDING_AS_OF_DATE = DATEADD( ss, -1, DATEADD( d, 1, @TO_DATE ) )

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@BEGINIG_AS_OF_DATE,101) AS DATETIME) )
	BEGIN
		IF @PROCESS_TYPE <> 'RMA'
		BEGIN
			SET @WIP_RESULT_STRING = 
				'INSERT INTO #BEGINING_WIP_RESULTS SELECT Lot_Number, Qty_In, Part_Number FROM 
				Lot_Table LT, Product P, Process_Step PS, Location L WHERE LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
				AND P.P_Type IS NULL AND ( LT.Lot_Current_Status = ''Running'' OR Lot_Current_Status = ''Hold'' )'
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''

			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = 'INSERT INTO #BEGINING_WIP_RESULTS SELECT Lot_Number, Ship_Qty, Part_Number FROM 
			Shipped_Lot SL, Lot_Table LT, Product P, Process_Step PS, Location L WHERE 
			SL.Lot_Id = Lt.Lot_ID AND LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID 
			AND Lt.Location_ID = L.Location_ID AND P.P_Type IS NULL  AND SL.Status <> ''Close'''

			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
		END
		ELSE
		BEGIN
			SET @WIP_RESULT_STRING = 
			'INSERT INTO #BEGINING_WIP_RESULTS SELECT RRLT.Lot_Number, RRLT.Qty_In, P.Part_Number FROM RMA_RECEIVE_LOT_table RRLT, PRODUCT P, LOCATION L WHERE
			RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID AND QTY_IN > 0 '
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = ''
			
		END
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @BEGINIG_AS_OF_DATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @BEGINIG_AS_OF_DATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		
		IF @PROCESS_TYPE <> 'RMA'
		BEGIN		
			SET @WIP_RESULT_STRING = 'INSERT INTO #BEGINING_WIP_RESULTS SELECT Lot_Number, Qty_In, Part_Number FROM 
				Lot_Table_History LT, Product P, Process_Step PS, Location L WHERE LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
				AND P.P_Type IS NULL AND ( LT.Lot_Current_Status = ''Running'' OR Lot_Current_Status = ''Hold'' )'
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + 'AND LT.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE LTH1.Lot_ID = Lt.Lot_ID AND LTH1.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''')'

			SET @TRANSIT_RESULT_STRING = 'INSERT INTO #BEGINING_WIP_RESULTS SELECT Lot_Number, Ship_Qty, Part_Number FROM 
			SHIPPED_LOT_HISTORY SL, Lot_Table LT, Product P, Process_Step PS, Location L WHERE 
			SL.Lot_Id = Lt.Lot_ID AND LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID 
			AND Lt.Location_ID = L.Location_ID AND P.P_Type IS NULL  AND SL.Status <> ''Close'''

			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND ( SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') ' 
		END
		ELSE
		BEGIN
			SET @WIP_RESULT_STRING = 
			'INSERT INTO #BEGINING_WIP_RESULTS SELECT RRLT.Lot_Number, RRLT.Qty_In, P.Part_Number FROM RMA_RECEIVE_LOT_History_Table RRLT, PRODUCT P, LOCATION L WHERE
			RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID AND QTY_IN > 0 '
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END
			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + 'AND RRLT.TimeStamp = ( SELECT MAX(RRLT1.Timestamp) FROM RMA_RECEIVE_LOT_History_Table RRLT1 WHERE RRLT1.RMA_Receive_Lot_ID = RRLT.RMA_Receive_Lot_ID AND RRLT1.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''')'
			SET @TRANSIT_RESULT_STRING = ''
		END
	END

	--PRINT 'Begining Qty'

	--PRINT @WIP_RESULT_STRING
	--PRINT @TRANSIT_RESULT_STRING


	EXEC (@WIP_RESULT_STRING)
	EXEC (@TRANSIT_RESULT_STRING)

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@ENDING_AS_OF_DATE,101) AS DATETIME) ) --CURRENT WIP
	BEGIN
		IF @PROCESS_TYPE <> 'RMA'
		BEGIN	
			SET @WIP_RESULT_STRING = N'
				INSERT INTO #ENDING_WIP_RESULTS SELECT Lot_Number, Qty_In, Part_Number FROM 
				Lot_Table LT, Product P, Process_Step PS, Location L WHERE LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
				AND P.P_Type IS NULL AND ( LT.Lot_Current_Status = ''Running'' OR Lot_Current_Status = ''Hold'' )'
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''

			SET @TRANSIT_RESULT_STRING = N'INSERT INTO #ENDING_WIP_RESULTS SELECT Lot_Number, Ship_Qty, Part_Number FROM 
			Shipped_Lot SL, Lot_Table LT, Product P, Process_Step PS, Location L WHERE 
			SL.Lot_Id = Lt.Lot_ID AND LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID 
			AND Lt.Location_ID = L.Location_ID AND P.P_Type IS NULL AND SL.Status <> ''Close'''

			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
		END
		ELSE
		BEGIN
			SET @WIP_RESULT_STRING = 
			'INSERT INTO #ENDING_WIP_RESULTS SELECT RRLT.Lot_Number, RRLT.Qty_In, P.Part_Number FROM RMA_RECEIVE_LOT_table RRLT, PRODUCT P, LOCATION L WHERE
			RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID AND QTY_IN > 0 '
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = ''
		END

	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ENDING_AS_OF_DATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ENDING_AS_OF_DATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		IF @PROCESS_TYPE <> 'RMA'
		BEGIN
			SET @WIP_RESULT_STRING = N'
				INSERT INTO #ENDING_WIP_RESULTS SELECT Lot_Number, Qty_In, Part_Number FROM 
				Lot_Table_History LT, Product P, Process_Step PS, Location L WHERE LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID 
				AND P.P_Type IS NULL AND ( LT.Lot_Current_Status = ''Running'' OR Lot_Current_Status = ''Hold'' )'
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE LTH1.Lot_Id = LT.Lot_ID AND LTH1.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''')'

			SET @TRANSIT_RESULT_STRING = N'INSERT INTO #ENDING_WIP_RESULTS SELECT Lot_Number, Ship_Qty, Part_Number FROM 
			SHIPPED_LOT_HISTORY SL, Lot_Table LT, Product P, Process_Step PS, Location L WHERE 
			SL.Lot_Id = Lt.Lot_ID AND LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID 
			AND Lt.Location_ID = L.Location_ID AND P.P_Type IS NULL AND SL.Status <> ''Close'''

			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END

			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
			SET @TRANSIT_RESULT_STRING = @TRANSIT_RESULT_STRING + ' AND ( SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') ' 
		END
		ELSE
		BEGIN
			SET @WIP_RESULT_STRING = 
			'INSERT INTO #ENDING_WIP_RESULTS SELECT RRLT.Lot_Number, RRLT.Qty_In, P.Part_Number FROM RMA_RECEIVE_LOT_History_Table RRLT, PRODUCT P, LOCATION L WHERE
			RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID AND QTY_IN > 0 '
			IF @BASE_PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
			END

			IF @PRODUCT_ID <> '0' 
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
			END

			IF @VENDOR_NAME <> '0'
			BEGIN
				SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
			END
			SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + 'AND RRLT.TimeStamp = ( SELECT MAX(RRLT1.Timestamp) FROM RMA_RECEIVE_LOT_History_Table RRLT1 WHERE RRLT1.RMA_Receive_Lot_ID = RRLT.RMA_Receive_Lot_ID AND RRLT1.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''' AND '''+ CONVERT(VARCHAR,@END_DATE)+''')'
			SET @TRANSIT_RESULT_STRING = ''
		END
	END

	--PRINT 'Ending Qty'

	--PRINT @WIP_RESULT_STRING
	--PRINT @TRANSIT_RESULT_STRING
	EXEC (@WIP_RESULT_STRING)
	EXEC (@TRANSIT_RESULT_STRING)

-------------------------------------------BEGINING AND ENDING INVENTORY CODE--------------------------------------------------------------------------

	INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, BEGINING_QTY )
		SELECT PART_NUMBER, LOT_NUMBER, QTY_IN FROM  #BEGINING_WIP_RESULTS-- WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	INSERT INTO #RESULTS_TABLE ( Part_Number,LOT_NUMBER, ENDING_QTY )
		SELECT PART_NUMBER, LOT_NUMBER, QTY_IN FROM  #ENDING_WIP_RESULTS-- WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID

	DECLARE @SCRAP_LOT_STRING VARCHAR(1000)
	SET @SCRAP_LOT_STRING = N'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, SCRAP_QTY )
		SELECT P.Part_Number, LT.LOT_NUMBER, SL.SL_SCRAPPED_DIE_QTY - ISNULL(( SELECT Qty_In FROM NONVALUE_WIP_TABLE WHERE Lot_ID = SL.Lot_ID AND CAST(CONVERT(VARCHAR,TIMESTAMP,101) AS DATETIME) >='''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' AND CAST(CONVERT(VARCHAR,TIMESTAMP,101) AS DATETIME) <= ''' + CONVERT(VARCHAR,@TO_DATE , 101)+''' ), 0) FROM SCRAPPED_LOTS SL, LOT_TABLE LT, Product P, Location L, Process_Step PS
						WHERE SL.LOT_ID = LT.LOT_ID AND LT.ProcessStep_Id = P.ProcessStep_ID AND Lt.Location_ID = L.Location_ID
							AND P.ProcessStep_Id = PS.ProcessStep_ID AND P.P_Type IS NULL 
							AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +' ''
							AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''''

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @SCRAP_LOT_STRING = @SCRAP_LOT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @SCRAP_LOT_STRING = @SCRAP_LOT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @SCRAP_LOT_STRING = @SCRAP_LOT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		SET @SCRAP_LOT_STRING = @SCRAP_LOT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
	
		PRINT 		@SCRAP_LOT_STRING
		EXEC (@SCRAP_LOT_STRING)


	DECLARE @RMA_LOT_STRING VARCHAR(1000)
	SET @RMA_LOT_STRING = N'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, IN_FROM_RMA )
		SELECT  P.Part_Number, RDT.Lot_Number, RDT.Qty_In FROM RMA_Dispose_Table RDT, Lot_Table LT, Product P, Process_Step PS, Location L 
		WHERE RDT.Lot_ID = Lt.Lot_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.ProcessStep_ID = P. ProcessStep_ID AND Lt.Location_ID = L.Location_ID
		AND P.P_Type IS NULL 
		AND CAST(CONVERT(VARCHAR,RDT.Timestamp,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +' ''
		AND CAST(CONVERT(VARCHAR,RDT.Timestamp,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''''
		
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @RMA_LOT_STRING = @RMA_LOT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @RMA_LOT_STRING = @RMA_LOT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @RMA_LOT_STRING = @RMA_LOT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		SET @RMA_LOT_STRING = @RMA_LOT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
	
		--PRINT @RMA_LOT_STRING
		EXEC (@RMA_LOT_STRING)

-- Die Bank
	IF ( @PROCESS_TYPE = 'Die Bank' )
	BEGIN
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, IN_QTY )
		SELECT P.Part_Number, LT.Lot_Number, Qty_In FROM LOT_TABLE LT, Product P, Location L, Process_Step PS
		WHERE Lt.ProcessStep_ID = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_Id = L.Location_ID
		AND P.P_Type IS NULL AND Lt.Fab_Lot_Number IN ( SELECT DISTINCT FAB_Lot_Number FROM SHIPPED_LOT SL, Lot_Table LT, Process_Step PS WHERE
		SL.Lot_ID = Lt.Lot_ID AND Lt.ProcessStep_ID = PS.ProcessStep_Id AND SL.Status = ''Close''
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
		AND PS.PS_type_name = ''FAB'' )'

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''

		--PRINT 		@IN_QTY_STRING

		EXEC (@IN_QTY_STRING)

		SET @OUT_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( P.Part_Number, LOT_NUMBER, OUT_QTY )
			SELECT P.Part_Number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, LOCATION L
					WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND PS.ProcessStep_Id = P.ProcessStep_ID
					AND L.LOCATION_ID = LT.LOCATION_ID AND P.P_Type IS NULL 
					AND SL.SL_NEXT_PROCESS <> ''DIE BANK'' 
					AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +' '' AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''''
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''
		EXEC (@OUT_QTY_STRING)			
	END
	ELSE IF ( @PROCESS_TYPE = 'Assembly' )
	BEGIN 
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, IN_QTY )
		SELECT (Select TOP 1 P.Part_Number from Product P, Assm_release_Table ART, Assm_release_lot_Table ARLT where Art.Assembly_release_Id = ARLT.Assembly_release_ID and Art.ProcessStep_ID = P.ProcessStep_ID and ARLT.Shipped_lot_Number = Lt.Lot_Number), LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P, LOCATION L
		WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID AND P.P_Type IS NULL 
		AND PS.PS_TYPE_NAME = ''DIE BANK'' AND SL_NEXT_PROCESS <> ''Die Bank''
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
		AND LT.LOT_NUMBER IN ( SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
		WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> ''CANCELLED'' )' 

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,0 ) WHERE PS_TYPE = 'Die Bank'
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+  @TEMP_PROCESSSTEP_ID
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND SL.SL_NextLocation = '''+ @VENDOR_NAME + ''''
		END
		--PRINT '@IN_QTY_STRING ' + @IN_QTY_STRING

		EXEC (@IN_QTY_STRING)

		SET @OUT_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, OUT_QTY )
			SELECT P.Part_Number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, Product P
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE''
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND L.LOCATION_ID = LT.LOCATION_ID AND P.P_Type IS NULL 
				AND PS.PS_TYPE = ''ASSEMBLY''
				AND SL.SL_NEXT_PROCESS <> ''ASSEMBLY''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''''

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''

		--print 'Assembly @OUT_QTY_STRING:::'+ @OUT_QTY_STRING
		EXEC (@OUT_QTY_STRING)	

		DECLARE @DBPART_CURSOR CURSOR
		DECLARE @Assy_Part_Number VARCHAR(50)
		DECLARE @DB_PART_NUMBER VARCHAR(50)
		
		SET @DBPART_CURSOR = CURSOR FOR SELECT DISTINCT Part_Number FROM #RESULTS_TABLE
		OPEN @DBPART_CURSOR
		FETCH NEXT FROM @DBPART_CURSOR INTO @Assy_Part_Number
		WHILE @@FETCH_STATUS = 0
		BEGIN

			SELECT @DB_PART_NUMBER = PART_NUMBER FROM PRODUCT P, PROCESS_STEP_TRAVERSE_TABLE PSTT, PROCESS_STEP PS
										WHERE P.PROCESSSTEP_ID = PSTT.PROCESSSTEP_ID
											AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
											AND PSTT.NEXT_PROCESSSTEP_ID = ( SELECT TOP 1 P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.Part_Number = @Assy_Part_Number AND PS.PS_Type = 'Assembly'  )
											AND PS.PS_TYPE_NAME = 'Die Bank'

			UPDATE #RESULTS_TABLE SET DB_PART_NUMBER = @DB_PART_NUMBER WHERE Part_Number = @Assy_Part_Number
			SET @Assy_Part_Number = ''
			SET @DB_PART_NUMBER = ''
			FETCH NEXT FROM @DBPART_CURSOR INTO @Assy_Part_Number
		END
		CLOSE @DBPART_CURSOR
		DEALLOCATE @DBPART_CURSOR

	END
	ELSE IF ( @PROCESS_TYPE = 'FT' )
	BEGIN
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_Number, LOT_NUMBER, IN_QTY )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE''
				AND SL.SL_NEXT_PROCESS = ''FT'' 
				AND Lt.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND LT.ProcessStep_ID = P.ProcessStep_ID
				AND LT.LOCATION_ID = L.LOCATION_ID AND P.P_Type IS NULL 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND PS.PS_Type_Name = ''Assembly'''

		
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,0 ) WHERE PS_TYPE = 'Assembly'
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+ @TEMP_PROCESSSTEP_ID
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--PRINT '@IN_QTY_STRING:::::'+ @IN_QTY_STRING

		EXEC (@IN_QTY_STRING)

		SET @OUT_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( P.Part_Number, LOT_NUMBER, OUT_QTY )
			SELECT P.Part_Number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.ProcessStep_ID = P.ProcessStep_ID
				AND PS.PS_TYPE = ''FT''
				AND SL.SL_NEXT_PROCESS <> ''FT'' AND P.P_Type IS NULL 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND L.LOCATION_ID = LT.LOCATION_ID'

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--print '@OUT_QTY_STRING::::::' + @OUT_QTY_STRING
		EXEC (@OUT_QTY_STRING)	

		/*SET @OUT_TO_OTHER_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( P.Part_Number, LOT_NUMBER, OUT_TO_OTHER )
		SELECT P.Part_Number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.ProcessStep_ID = P.ProcessStep_ID
				AND PS.PS_TYPE = ''FT''
				AND SL.SL_NEXT_PROCESS <> ''FT'' AND P.P_Type IS NULL 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND L.LOCATION_ID = LT.LOCATION_ID'	*/

		SET @IN_FROM_OTHER_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( P.Part_Number, LOT_NUMBER, IN_From_Other )	
		SELECT ISNULL(( select Part_number from Process_Step_traverse_table PSTT, Process_Step PS, Product PP where 
		PSTT.processStep_Id = PP.ProcessStep_ID AND PP.ProcessStep_Id = PS.ProcessStep_ID and ( PS.Ps_Type = ''FT'' or PS.Ps_Type = ''FPO'' ) and
		next_processStep_Id = P.ProcessStep_ID ), p.pART_NUMBER), LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P, LOCATION L
		WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE''
		AND SL.SL_NEXT_PROCESS = ''FT'' 
		AND Lt.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.ProcessStep_ID = P.ProcessStep_ID
		AND LT.LOCATION_ID = L.LOCATION_ID AND P.P_Type IS NULL 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
		AND PS.PS_Type_Name <> ''Assembly'' AND PS.PS_Type <> ''FT'''
		
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_FROM_OTHER_QTY_STRING = @IN_FROM_OTHER_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @TEMP_PROCESSSTEP_ID = ''
			SET @TEMP_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,1 ) WHERE PS_TYPE IN ( 'FPO', 'FG', 'Tape & Reel' )
			OPEN @TEMP_CURSOR
			FETCH NEXT FROM @TEMP_CURSOR INTO @OTHER_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @TEMP_PROCESSSTEP_ID = @TEMP_PROCESSSTEP_ID + CONVERT(VARCHAR,@OTHER_PROCESSSTEP_ID) + ','
				FETCH NEXT FROM @TEMP_CURSOR INTO @OTHER_PROCESSSTEP_ID
			END
			
			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
			IF @TEMP_PROCESSSTEP_ID IS NOT NULL AND SUBSTRING (@TEMP_PROCESSSTEP_ID, LEN(@TEMP_PROCESSSTEP_ID), LEN(@TEMP_PROCESSSTEP_ID)) = ','
			BEGIN
				SET @TEMP_PROCESSSTEP_ID = SUBSTRING (@TEMP_PROCESSSTEP_ID, 0, LEN(@TEMP_PROCESSSTEP_ID))
				SET @IN_FROM_OTHER_QTY_STRING = @IN_FROM_OTHER_QTY_STRING + ' AND LT.ProcessStep_ID IN ( '+ @TEMP_PROCESSSTEP_ID + ')'
			END
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_FROM_OTHER_QTY_STRING = @IN_FROM_OTHER_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--PRINT '@IN_FROM_OTHER_QTY_STRING:::::'+ @IN_FROM_OTHER_QTY_STRING

		EXEC (@IN_FROM_OTHER_QTY_STRING)

	END
	ELSE IF ( @PROCESS_TYPE = 'TAPE & REEL' )
	BEGIN
		DECLARE @RECEIVED_FROM_FTFG_QTY INT
		DECLARE @RECEIVED_FROM_FFG_QTY INT

		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, IN_QTY )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, Location L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE''
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.ProcessStep_ID = P.ProcessStep_ID
				AND LT.Location_ID = L.Location_ID AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS = ''Tape & Reel'' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND PS.PS_TYPE_NAME  = ''Test'''
		
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,0 ) WHERE PS_TYPE = 'FT'
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+ @TEMP_PROCESSSTEP_ID
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		--PRINT '@IN_QTY_STRING from TEST:::::::::'+@IN_QTY_STRING
		EXEC (@IN_QTY_STRING)
		
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, IN_FROM_FG )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, Location L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE''
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND Lt.Location_ID = L.Location_ID AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS = ''Tape & Reel''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND PS.PS_TYPE_NAME  = ''FINISHED GOODS'''

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @TEMP_PROCESSSTEP_ID = ''
			SET @TEMP_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,1 ) WHERE PS_TYPE IN ( 'FG' )
			OPEN @TEMP_CURSOR
			FETCH NEXT FROM @TEMP_CURSOR INTO @OTHER_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @TEMP_PROCESSSTEP_ID = @TEMP_PROCESSSTEP_ID + CONVERT(VARCHAR,@OTHER_PROCESSSTEP_ID) + ','
				FETCH NEXT FROM @TEMP_CURSOR INTO @OTHER_PROCESSSTEP_ID
			END
			
			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
			IF @TEMP_PROCESSSTEP_ID IS NOT NULL AND SUBSTRING (@TEMP_PROCESSSTEP_ID, LEN(@TEMP_PROCESSSTEP_ID), LEN(@TEMP_PROCESSSTEP_ID)) = ','
			BEGIN
				SET @TEMP_PROCESSSTEP_ID = SUBSTRING (@TEMP_PROCESSSTEP_ID, 0, LEN(@TEMP_PROCESSSTEP_ID))
				SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+ @TEMP_PROCESSSTEP_ID
			END
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--PRINT '@IN_QTY_STRING from FG:::::::::'+@IN_QTY_STRING
		EXEC (@IN_QTY_STRING)

		SET @OUT_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, OUT_QTY )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND PS.PS_TYPE = ''Tape & Reel'' AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS <> ''Tape & Reel''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND L.LOCATION_ID = LT.LOCATION_ID AND ISNULL(Transfer_To_Bulk, 0) = 0 '


		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		--print '@OUT_QTY_STRING::' + @OUT_QTY_STRING
		EXEC (@OUT_QTY_STRING)	

		-- Transfers to Bulk
		SET @TRANSFER_TO_BULK_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, Transfer_To_Bulk )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND PS.PS_TYPE = ''Tape & Reel'' AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS <> ''Tape & Reel''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND L.LOCATION_ID = LT.LOCATION_ID AND ISNULL(Transfer_To_Bulk, 0) = 1 '


		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @TRANSFER_TO_BULK_STRING = @TRANSFER_TO_BULK_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @TRANSFER_TO_BULK_STRING = @TRANSFER_TO_BULK_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @TRANSFER_TO_BULK_STRING = @TRANSFER_TO_BULK_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		--print '@TRANSFER_TO_BULK_STRING::' + @TRANSFER_TO_BULK_STRING
		EXEC (@TRANSFER_TO_BULK_STRING)	

	END
	ELSE IF ( @PROCESS_TYPE = 'FG' )
	BEGIN
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, IN_QTY )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, Location L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID	
				AND LT.Location_ID = L.Location_ID AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS = ''FG'' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND PS.PS_Type_Name = ''Test'''

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @TEMP_PROCESSSTEP_ID = null
			SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,0 ) WHERE PS_TYPE = 'FT'

			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND LT.ProcessStep_ID = '+ @TEMP_PROCESSSTEP_ID
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--PRINT 	'IN Qty :'+ 	@IN_QTY_STRING
		EXEC (@IN_QTY_STRING)

		DECLARE @LOT_ID_CURSOR CURSOR
		DECLARE @PART_NUMBER VARCHAR(50)
		DECLARE @LOT_NUMBER VARCHAR(50)
		DECLARE @SHIP_QTY INT
		DECLARE @CLOSE_DATE DATETIME
		DECLARE @PROCESSSTEP_ID INT
		DECLARE @LOT_ID INT
		DECLARE @PREV_QTY INT
		DECLARE @CURRENT_QTY INT
		DECLARE @LOTSHIP_QTY INT
		DECLARE @LOTTRANSFER_QTY INT

		SET @CURSOR_STRING = N'DECLARE LOT_CURSOR CURSOR FOR SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY, Close_Date, PS.ProcessStep_ID 
		FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, Product P, Location L
		WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
		AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
		AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND Lt.Location_ID = L.Location_ID
		AND SL.SL_NEXT_PROCESS = ''FG'' AND P.P_Type IS NULL 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
		AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
		AND PS.PS_TYPE_NAME = ''CLASS STORE'' '

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @TEMP_PROCESSSTEP_ID = null
			SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,0 ) WHERE PS_TYPE = 'Tape & Reel'
			IF @TEMP_PROCESSSTEP_ID IS NULL
			BEGIN
				SELECT TOP 1 @TEMP_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_get_process_flows_by_processstep_Id ( @PRODUCT_ID,1 ) WHERE PS_TYPE = 'Tape & Reel'
			END
			SET @CURSOR_STRING = @CURSOR_STRING + ' AND LT.ProcessStep_ID = '+ @TEMP_PROCESSSTEP_ID
			--SET @CURSOR_STRING = @CURSOR_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @CURSOR_STRING = @CURSOR_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		EXECUTE SP_EXECUTESQL @CURSOR_STRING

		OPEN LOT_CURSOR 
		FETCH NEXT FROM LOT_CURSOR INTO @PART_NUMBER, @LOT_NUMBER, @SHIP_QTY, @CLOSE_DATE, @PROCESSSTEP_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @LOT_ID_CURSOR = CURSOR FOR SELECT Lt.Lot_ID FROM Lot_Table Lt, Process_Step PS, Product P WHERE Lt.ProcessStep_Id = PS.ProcessStep_ID AND PS.ProcessStep_ID = P.ProcessStep_ID AND Lot_Number = @LOT_NUMBER AND PS_Type = 'FG' AND P.Part_NUMBER <> @PART_NUMBER
			OPEN @LOT_ID_CURSOR
			FETCH NEXT FROM @LOT_ID_CURSOR INTO @LOT_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				
				SET @LOTSHIP_QTY = 0
				SET @CURRENT_QTY = 0
				SET @PREV_QTY = 0
				SET @LOTTRANSFER_QTY = 0
			
				SELECT @PREV_QTY = ISNULL(QTY_IN, 0) FROM LOT_TABLE_HISTORY WHERE LOT_ID = @LOT_ID AND TIMESTAMP < @CLOSE_DATE AND TIMESTAMP > DATEADD( D, -1, @CLOSE_DATE ) AND LOT_CURRENT_STATUS = 'RUNNING'
				SELECT @CURRENT_QTY = ISNULL(QTY_IN, 0) FROM LOT_TABLE_HISTORY WHERE LOT_ID = @LOT_ID AND TIMESTAMP > @CLOSE_DATE AND TIMESTAMP < DATEADD( D, 1, @CLOSE_DATE ) AND LOT_CURRENT_STATUS = 'RUNNING'
				
				SELECT @LOTSHIP_QTY = SUM(ISNULL(CSLNT_QTY_PULL, 0)) FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, Customer_Shipping_Table CST
				WHERE CSLNT.CSLIT_ID = CSLIT.CSLIT_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID 
				AND CSLNT_STATUS = 'SHIPPED' AND CST_CONFIRMED_ON >= @CLOSE_DATE AND CST_CONFIRMED_ON < DATEADD( D, 1, CONVERT(VARCHAR, @CLOSE_DATE, 101 ) ) AND CSLNT.LOT_ID = @LOT_ID

				SELECT @LOTTRANSFER_QTY = SHIP_QTY FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND SHIP_DATE = CONVERT(VARCHAR, @CLOSE_DATE, 101 )

				IF @PREV_QTY IS NULL
				BEGIN
					SET @PREV_QTY = 0
				END
				IF @CURRENT_QTY IS NULL
				BEGIN
					SET @CURRENT_QTY = 0
				END
				IF @LOTSHIP_QTY IS NULL
				BEGIN
					SET @LOTSHIP_QTY = 0
				END
				IF @LOTTRANSFER_QTY IS NULL
				BEGIN
					SET @LOTTRANSFER_QTY = 0
				END
			
				IF( ( @CURRENT_QTY + @LOTSHIP_QTY + @LOTTRANSFER_QTY - @PREV_QTY ) = @SHIP_QTY )
				BEGIN
					SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT P, LOT_TABLE LT WHERE P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LOT_ID = @LOT_ID
				END
		 
				FETCH NEXT FROM @LOT_ID_CURSOR INTO @LOT_ID
			END
			CLOSE @LOT_ID_CURSOR
			DEALLOCATE @LOT_ID_CURSOR

			INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, IN_FROM_TR ) VALUES ( @PART_NUMBER, @LOT_NUMBER, @SHIP_QTY )
			FETCH NEXT FROM LOT_CURSOR INTO @PART_NUMBER, @LOT_NUMBER, @SHIP_QTY, @CLOSE_DATE, @PROCESSSTEP_ID	
		END
		CLOSE LOT_CURSOR
		DEALLOCATE LOT_CURSOR
		
		SET @OUT_QTY_STRING = '
		INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, OUT_QTY )
			SELECT P.Part_number, LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, Product P 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = ''CLOSE'' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND PS.PS_TYPE = ''FG'' AND P.P_Type IS NULL 
				AND SL.SL_NEXT_PROCESS <> ''FG''
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ '''
				AND L.LOCATION_ID = LT.LOCATION_ID'

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		EXEC (@OUT_QTY_STRING)	
		--print @OUT_QTY_STRING
		SET @OUT_QTY_STRING = '
		INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, SHIPMENTS_TO_CUSTOMER )
			SELECT P.Part_Number, LT.LOT_NUMBER, SUM(CSLNT_QTY_PULL)
				FROM CUSTOMER_SHIPPING_TABLE CST, 
					CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,
					CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, 
					CUSTOMER_OPEN_ORDER_LINEITEM COOL, 
					PRODUCT P,
					LOT_TABLE LT,
					LOCATION L,
					Sales_order SO
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID 
					AND CST.CST_STATUS = ''SHIPPED''
					AND CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
					AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID		
					AND P.PRODUCT_ID = COOL.PRODUCT_ID	
					AND LT.LOT_ID = CSLNT.LOT_ID	
					AND CST.SalesOrder_Id = SO.SalesOrder_ID 	
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''  
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''' 
					AND CSLNT_QTY_PULL > 0
					AND SO.SO_IS_CONSIGNMENT <> 0
					AND L.LOCATION_ID = LT.LOCATION_ID'

		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
	
		SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' Group By Part_number, Lot_Number '	

		EXEC (@OUT_QTY_STRING)	

		--PRINT 'Ship to Customer ' + @OUT_QTY_STRING
					
	END
	ELSE IF ( @PROCESS_TYPE = 'RMA' )
	BEGIN
		SET @IN_QTY_STRING = 'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, SHIPMENTS_TO_CUSTOMER )
		SELECT P.Part_Number, RRLT.Lot_Number, RRLT.Qty_In + ISNULL((SELECT ISNULL(SUM(Qty_In), 0) FROM RMA_DISPOSE_TABLE WHERE RMA_Receive_Lot_ID = RRLT.RMA_Receive_Lot_ID ), 0) FROM RMA_RECEIVE_LOT_table RRLT, PRODUCT P, LOCATION L WHERE
		RRLT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND RRLT.LOCATION_ID = L.LOCATION_ID 
		AND CAST(CONVERT(VARCHAR,RRLT.DATE_IN,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''  
		AND CAST(CONVERT(VARCHAR,RRLT.DATE_IN,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''' '
		
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @IN_QTY_STRING = @IN_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
	
		--print @IN_QTY_STRING
	
		EXEC (@IN_QTY_STRING)

		-- OUT TO TEST
		SET @OUT_QTY_STRING = 	'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, OUT_QTY )
		SELECT P.Part_Number, RRLT.Lot_Number, RDT.Qty_IN FROM RMA_RECEIVE_LOT_table RRLT, RMA_DISPOSE_TABLE RDT, Location L, Product P, Process_Step PS, Lot_Table LT 
		WHERE RRLT.RMA_RECEIVE_LOT_ID = RDT.RMA_RECEIVE_LOT_ID AND L.Location_ID = RRLT.Location_ID AND	RRLT.ProcessStep_ID = P.ProcessStep_ID
		AND RDT.Lot_ID = Lt.Lot_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type_Name = ''TEST''
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''  
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''' '
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		EXEC (@OUT_QTY_STRING)
		
		-- OUT TO Tape & Reel
		SET @OUT_QTY_STRING = 	'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, In_from_TR )
		SELECT P.Part_Number, RRLT.Lot_Number, RDT.Qty_IN FROM RMA_RECEIVE_LOT_table RRLT, RMA_DISPOSE_TABLE RDT, Location L, Product P, Process_Step PS, Lot_Table LT 
		WHERE RRLT.RMA_RECEIVE_LOT_ID = RDT.RMA_RECEIVE_LOT_ID AND L.Location_ID = RRLT.Location_ID AND	RRLT.ProcessStep_ID = P.ProcessStep_ID
		AND RDT.Lot_ID = Lt.Lot_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type_Name = ''Class Store''
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''  
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''' '
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END

		--print @OUT_QTY_STRING
		EXEC (@OUT_QTY_STRING)

		-- OUT TO FG
		SET @OUT_QTY_STRING = 	'INSERT INTO #RESULTS_TABLE ( Part_number, LOT_NUMBER, In_from_FG )
		SELECT P.Part_Number,RRLT.Lot_Number, RDT.Qty_IN FROM RMA_RECEIVE_LOT_table RRLT, RMA_DISPOSE_TABLE RDT, Location L, Product P, Process_Step PS, Lot_Table LT 
		WHERE RRLT.RMA_RECEIVE_LOT_ID = RDT.RMA_RECEIVE_LOT_ID AND L.Location_ID = RRLT.Location_ID AND	RRLT.ProcessStep_ID = P.ProcessStep_ID
		AND RDT.Lot_ID = Lt.Lot_ID AND Lt.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type_Name = ''Finished Goods''
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +'''  
		AND CAST(CONVERT(VARCHAR,RDT.TimeStamp,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE , 101)+ ''' '
		IF @BASE_PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
		END

		IF @PRODUCT_ID <> '0' 
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND RRLT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PRODUCT_ID)
		END

		IF @VENDOR_NAME <> '0'
		BEGIN
			SET @OUT_QTY_STRING = @OUT_QTY_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
		END
		EXEC (@OUT_QTY_STRING)
	END

	--UPDATE #RESULTS_TABLE SET DB_PART_NUMBER = @DB_PART_NUMBER
	IF @BY_LOT = 0
	BEGIN
			SELECT Part_number, '' AS Lot_Number, SUM(ISNULL(BEGINING_QTY,0)) AS BEGINING_QTY, 
			SUM(ISNULL(IN_QTY,0)) AS IN_QTY, 
			SUM(ISNULL(OUT_QTY,0)) AS OUT_QTY, 
			SUM(ISNULL(IN_FROM_FG,0)) AS IN_FROM_FG,
			SUM(ISNULL(IN_FROM_TR,0)) AS IN_FROM_TR,
			SUM(ISNULL(SHIPMENTS_TO_CUSTOMER,0)) AS SHIPMENTS_TO_CUSTOMER,
			SUM(ISNULL(SCRAP_QTY,0)) AS SCRAP_QTY,
			SUM(ISNULL(ENDING_QTY,0)) AS ENDING_QTY,
			SUM(ISNULL(IN_FROM_RMA, 0)) AS IN_FROM_RMA,
			SUM(ISNULL(Transfer_To_Bulk, 0)) AS TRANSFER_TO_BULK,
			SUM(ISNULL(IN_From_Other, 0)) AS IN_FROM_OTHER,
			DB_PART_NUMBER
		FROM #RESULTS_TABLE 
		GROUP BY Part_Number, DB_PART_NUMBER
		Order by Part_number
			
	END
	ELSE
	BEGIN
		SELECT Part_number, LOT_NUMBER, SUM(ISNULL(BEGINING_QTY,0)) AS BEGINING_QTY, 
			SUM(ISNULL(IN_QTY,0)) AS IN_QTY, 
			SUM(ISNULL(OUT_QTY,0)) AS OUT_QTY, 
			SUM(ISNULL(IN_FROM_FG,0)) AS IN_FROM_FG,
			SUM(ISNULL(IN_FROM_TR,0)) AS IN_FROM_TR,
			SUM(ISNULL(SHIPMENTS_TO_CUSTOMER,0)) AS SHIPMENTS_TO_CUSTOMER,
			SUM(ISNULL(SCRAP_QTY,0)) AS SCRAP_QTY,
			SUM(ISNULL(ENDING_QTY,0)) AS ENDING_QTY,
			SUM(ISNULL(IN_FROM_RMA, 0)) AS IN_FROM_RMA,
			SUM(ISNULL(Transfer_To_Bulk, 0)) AS TRANSFER_TO_BULK,
			SUM(ISNULL(IN_From_Other, 0)) AS IN_FROM_OTHER,
			DB_PART_NUMBER
		FROM #RESULTS_TABLE  
		GROUP BY Part_Number, LOT_NUMBER, DB_PART_NUMBER
		Order by Part_number, Lot_Number
	END
	--select * from #RESULTS_TABLE
END